-----------------------------------------------------------------------------
-- Initial Setup

USE AdventureWorks
GO

-- Configure for CLR

EXEC sp_configure 'clr enabled', '1'
GO

RECONFIGURE
GO

-----------------------------------------------------------------------------
-- Stored Procedures

-- Testing

exec clrSimpleProcedure

-- Show SQL Server Profiler here!

-- Test other SqlPipe.Send options

EXEC clrGetVersion

EXEC clrGetEmployees

EXEC clrGetCustomRecord

-- Test parameters

EXEC clrInputStringParamProcedure

EXEC clrInputStringParamProcedure 'Richard'

DECLARE @Name nvarchar(4000)
EXECUTE [AdventureWorks].[dbo].[clrOutputStringParamProcedure] @Name OUTPUT
SELECT @Name

DECLARE @Name nvarchar(4000)
SET @Name = 'Richard'
EXECUTE [AdventureWorks].[dbo].clrInputOutputStringParamProcedure 
   @Name OUTPUT
SELECT @Name

DECLARE @Return int
EXECUTE @Return = [AdventureWorks].[dbo].[clrReturnValueProcedure]
SELECT @Return

-----------------------------------------------------------------------------
-- Scalar-Value Functions

-- clrSimpleFunction

SELECT dbo.clrSimpleFunction()

-- clrSimpleFunction assignment

DECLARE @Message nvarchar(50)
SELECT @Message = dbo.clrSimpleFunction()
PRINT @Message

-- clrNoDataAccessFunction

SELECT dbo.clrNoDataAccessFunction()

-- CoolEncrypt

SELECT dbo.clrCoolEncrypt('SQL Server 2005 Rocks!')
SELECT dbo.clrCoolEncrypt('A man a plan a canal Panama')

-----------------------------------------------------------------------------
-- Table-Value Functions

-- clrGetStrings

SELECT * FROM dbo.clrGetStrings('Test')
SELECT * FROM dbo.clrGetStrings('Red,Blue,Green')

-- Assign to table variable

SET NOCOUNT ON

DECLARE @Table Table (Value NVARCHAR(20))
INSERT @Table SELECT * FROM dbo.clrGetStrings('Red,Blue,Green')
SELECT * FROM @Table

-- Return Prime Numbers

SELECT * FROM dbo.clrGetPrimeNumbers(50)

-----------------------------------------------------------------------------
-- CLR Debugging

SELECT dbo.clrCoolEncrypt('SQL Server 2005 Rocks!')

